/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package hcc.app;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;

/**
 *
 * @author Guybrush
 */
public class Sql {

    private CORE app;
    private PreparedStatement ps;
    private ResultSet rs;


    public Sql(CORE app) {
        this.app = app;

        //Alle vorhandenen Tabellen der Datenbank mit ihren Namen angeben
        try {
            ps = app.con.prepareStatement(
                    "SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_schema = '" + app.adb + "';");
            rs = ps.executeQuery();
            while (rs.next()) {
                app.tabNames.add(rs.getString("table_name"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(CORE.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally {
            try {
                rs.close();
                ps.close();
            } catch (SQLException ex) {
                Logger.getLogger(Sql.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        // Datensätze aus der Datenbank extrahieren und in dynamische JTables[CORE] einfügen
        for (int it = 0; it < app.tabNames.size(); it++) {
            try {
                DefaultTableModel defTableModel = new DefaultTableModel(); //Tablemodel erstellen

                // Datensätze der jeweiligen Tabelle holen
                ps = app.con.prepareStatement("SELECT * FROM " + app.tabNames.get(it));
                rs = ps.executeQuery();
                ResultSetMetaData rsmd = null;
                int clmCnt = -1;
                try {
                    rsmd = rs.getMetaData();
                    clmCnt = rsmd.getColumnCount();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                if (rsmd == null || clmCnt == -1) {
                    throw new RuntimeException("rsmd is null");
                }
                try {
                    rs.first();
                    rs.last();
                    int rowCnt = rs.getRow();
                    rs.first();
                    // odata - für die Werte aus den Zeilen und Spalten der SQL-Tabellen
                    Object[][] odata = new Object[rowCnt][clmCnt];
                    // clmHeaders - für die Überschriften der Spalten
                    Object[] clmHeaders = new Object[clmCnt];
                    for (int i = 1; i <= clmCnt; i++) {
                        clmHeaders[i - 1] = rsmd.getColumnName(i);
                    }
                    int row = 0;
                    while (rs.next()) {
                        for (int i = 1; i <= clmCnt; i++) {
                            //System.out.print(rs.getString(i) + " ");
                            odata[row][i - 1] = rs.getString(i);
                        }
                        row++;
                        //System.out.println();
                    }
                    defTableModel.setDataVector(odata, clmHeaders);
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
                //Zusammenbauen der Tabelle
                JTable table = new JTable(defTableModel);
                //In die Arraylist in CORE einfügen
                app.tables.add(table);
            } catch (SQLException ex) {
                Logger.getLogger(Sql.class.getName()).log(Level.SEVERE, null, ex);
            }
            finally {
                try {
                    rs.close();
                    ps.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Sql.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }
    public void update(){
        JTable table = app.tables.get(app.tabFocus);
        TableModel tm = table.getModel();
        String s = (String) tm.getValueAt(app.rowNumber, app.colNumber);

        try {
            String sql = "UPDATE "+app.adb+"."+app.tabNameFocus+" SET "+app.colName+"=? WHERE "+app.tabNameFocus+"."+app.colName+"='"+s+"'";
            ps = app.con.prepareStatement(sql);
            ps.setString(1, app.changeMade);
            if (ps.executeUpdate() != 1) {
                    System.out.println("Daten nicht mehr vorhanden!");
                }
                ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(Sql.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    //UPDATE OHNE PRIMARY KEY:
    //UPDATE `tutorial`.`spiele` SET `Andere` = 'T-Rex - Virtual Shows' WHERE `spiele`.`Brettspiele` = 'Jurassic Park - Das Spiel' AND `spiele`.`Computerspiele` = 'Jurassic Park Operation Genesi' AND `spiele`.`Kartenspiele` = 'JP Quartett' AND `spiele`.`Andere` = 'T-Rex - Virtual Show' LIMIT 1 ;

    //UPDATE RICHTIG:
    //UPDATE `datenbank`.`tabelle` SET `SpalteDieÄnderungEnthält` = 'neuer Wert' WHERE `tabelle`.`spalteDesPrimKey` = ? (Zeile in der die Änderung ist)
}
